package bg.cinema_system.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bg.cinema_system.global.ConnectionJDBC;
import bg.cinema_system.pojo.UserTickets;
import bg.cinema_system.utils.Constants;

public class UserTicketsDAO {

	// Connect to DB and get list with information about the reserved tickets of
	// the user
	public static List<UserTickets> getUserTicketsList(int user_id)
			throws SQLException {
		// Get database connection
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException(
					"Class UserTicketsDAO: Can't get database connection");
		}

		String sqlStatement1 = "SELECT projections.projection_id, movies.name, movies.movie_type, movies.duration, halls.number_hall, reservations.expires_at, projections.projection_start "
				+ "FROM halls "
				+ "JOIN projections "
				+ "ON halls.hall_id = projections.fk_hall_id "
				+ "JOIN movies "
				+ "ON projections.fk_movie_id = movies.movie_id "
				+ "JOIN reservations "
				+ "ON projections.projection_id = reservations.fk_projection_id "
				+ "JOIN users "
				+ "ON reservations.fk_user_id = users.user_id "
				+ "WHERE users.user_id = ? "
				+ "AND projections.projection_start >= NOW() "
				+ "ORDER BY projections.projection_start ASC";
		
		String sqlStatement2 = "SELECT seats.seat_number, seats.fk_ticket_id "
				+ "FROM seats "
				+ "JOIN reservations "
				+ "ON reservations.reservation_id = seats.fk_reservation_id "
				+ "JOIN projections "
				+ "ON projections.projection_id = reservations.fk_projection_id "	
				+ "WHERE projections.projection_id = ? "
				+ "AND reservations.fk_user_id = ? ";

		PreparedStatement ps1 = dbConnection.prepareStatement(sqlStatement1);
		ps1.setInt(1, user_id);

		// Get customer data from database
		ResultSet result1 = ps1.executeQuery();

		List<UserTickets> userTicketsList = new ArrayList<UserTickets>();
		int br = 0;

		while (result1.next()) {
			UserTickets userTickets = new UserTickets();

			// Format the date accordingly
			String projectionStart = result1.getTimestamp("projection_start").toString();
			if (projectionStart.contains(".")) {
				projectionStart = projectionStart.substring(0, projectionStart.indexOf('.'));
			}
			userTickets.setReserved_projection_start(projectionStart);

			userTickets.setNumber(String.valueOf(++br));
			userTickets.setUser_id((int) user_id);
			userTickets.setReserved_projection_id(result1.getString("projection_id"));
			userTickets.setReserved_name(result1.getString("name"));
			userTickets.setReserved_movie_type(result1.getString("movie_type"));
			userTickets.setReserved_duration(result1.getTime("duration"));
			userTickets.setReserved_number_hall(result1.getLong("number_hall"));
			
			PreparedStatement ps2 = dbConnection.prepareStatement(sqlStatement2);
			ps2.setInt(1, Integer.parseInt(userTickets.getReserved_projection_id()));
			ps2.setInt(2, user_id);

			ResultSet result2 = ps2.executeQuery();
			
			int fk_ticket_id = 0;
	
			while (result2.next()) {
				if (userTickets.getReserved_seat_number() != null && !"".equals(userTickets.getReserved_seat_number())) {
					userTickets.setReserved_seat_number(userTickets.getReserved_seat_number()
							+ ", " + result2.getLong("seat_number"));
				} else {
					userTickets.setReserved_seat_number("" + result2.getLong("seat_number"));
				}
				
				fk_ticket_id = result2.getInt("fk_ticket_id");
			}
			
			if (fk_ticket_id == 0) {
				userTickets.setReserved_ticket_price("-");

				// Format the date accordingly
				String reservationExpiresAt = result1.getTimestamp("expires_at").toString();
				if (reservationExpiresAt.contains(".")) {
					reservationExpiresAt = reservationExpiresAt.substring(0, reservationExpiresAt.indexOf('.'));
				}
				userTickets.setReserved_ticket_status(Constants.TICKET_RESERVED);
				userTickets.setReserved_expires_at(reservationExpiresAt);
			} else {
				userTickets.setReserved_ticket_price(Constants.TICKET_PRICE + " лв.");
				userTickets.setReserved_ticket_status(Constants.TICKET_PURCHASED);
				userTickets.setReserved_expires_at("-");
			}

			userTicketsList.add(userTickets);
			
			// Close preparedStatement2
			ps2.close();
		}

		// Close preparedStatement1
		ps1.close();
		dbConnection.close();

		return userTicketsList;
	}

	public static boolean buyTicket(int user_id, int projection_id, int ticket_price) throws SQLException {
		
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException("Class UserTicketsDAO: Can't get database connection");
		}

		String sqlStatement = "INSERT INTO cinema.tickets " 
		+ " (price, fk_user_id, fk_projection_id) "
		+ " VALUES (?, ?, ?)";

		PreparedStatement ps = dbConnection.prepareStatement(sqlStatement);
		ps.setInt(1, ticket_price);
		ps.setInt(2, user_id);
		ps.setInt(3, projection_id);

		int result = ps.executeUpdate();

		if (result != 0) {
			return true;
		} else {
			return false;
		}
	}

	public static int getTicketId(int projection_id, int user_id) throws SQLException {

		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException(
					"Class UserTicketsDAO: Can't get database connection");
		}

		String sqlStatement = "SELECT tickets.ticket_id FROM cinema.tickets "
				+ "WHERE tickets.fk_user_id = ? "
				+ "AND tickets.fk_projection_id = ?";

		PreparedStatement ps = dbConnection.prepareStatement(sqlStatement);
		ps.setInt(1, user_id);
		ps.setInt(2, projection_id);

		// Get customer data from database
		ResultSet result = ps.executeQuery();

		int ticket_id = -1;

		if (result.next()) {
			ticket_id = result.getInt("ticket_id");
		}

		return ticket_id;
	}
}
